package com.ys.controller.api.base;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.apache.shiro.authz.annotation.RequiresPermissions;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.bo.dto.NameValueDto;
import com.ys.config.MenuId;
import com.ys.entity.base.TbReservedPay;
import com.ys.entity.base.TbReservedPayItem;
import com.ys.entity.finance.TcwPartnerBankInfo;
import com.ys.entity.sys.TbCompanyBankInfo;
import com.ys.entity.sys.TsUserChange;
import com.ys.entity.sys.YsCol;
import com.ys.entity.sys.YsGridCol;
import com.ys.enume.sys.YsColEnum;
import com.ys.service.base.TbReservedPayService;
import com.ys.service.sys.CommonApiService;
import com.ys.service.sys.PdfService;
import com.ys.service.sys.UploadService;
import com.ys.util.DateUtil;
import com.ys.util.ExportExcelUtils;
import com.ys.util.ExportMxExcelUtils;
import com.ys.util.admin.ConditionUtil;
import com.ys.util.shiro.ShiroUtils;
import com.ys.vo.LayuiTableVoJA;
import com.ys.vo.R;

/**
 * 公积金付款单
 */
@Controller
@RequestMapping("/api/inter/tbReservedPay")
public class ApiInterTbReservedPayController {
    private static final Logger logger = LoggerFactory.getLogger(ApiInterTbReservedPayController.class);

    @Autowired
    private CommonApiService<TbReservedPay> commonApiService;

    @Autowired
    private TbReservedPayService tbReservedPayService;

    @Autowired
    private PdfService pdfService;

    @RequiresPermissions("tbReservedPay:view")
    @RequestMapping("/search")
    @ResponseBody
    public LayuiTableVoJA search(@ModelAttribute TbReservedPay cond,String conditions){
        String _ys_createUserInfo = ConditionUtil.getConditionValue(conditions, "_ys_createUserInfo");
        String _ys_orgName = ConditionUtil.getConditionValue(conditions, "_ys_orgName");

        if (_ys_createUserInfo != null && _ys_createUserInfo.length() != 0) {
            cond.where("t2.UserId LIKE '%" + _ys_createUserInfo + "%'" + " OR  t2.UserName LIKE '%" + _ys_createUserInfo + "%'");
        }
        if (_ys_orgName != null && _ys_orgName.length() != 0) {
            cond.where("(SELECT O.OrgName FROM TB_ReservedPayItem rpi LEFT JOIN TB_UserMonthPerformance R ON R.id = rpi.UserMonthPerformanceId LEFT JOIN TS_OrganizationInfo O ON O.OrgId = R.OrgId WHERE rpi.ReservedPayBid = a.bid GROUP BY O.OrgName) LIKE'%" + _ys_orgName + "%'" );
        }
        cond.setOrderby("A.createTime DESC")
        .addLeftJoin("YS_ProcInst Ysp ON Ysp.bid = A.bid")//流程表
        .addField("Ysp.updateTime AS updateTime1")
        .addLeftJoin("TB_CompanyBankInfo cbi ON cbi.id = A.CompanyBankId")//公司银行信息
        .addField("cbi.Vyhzh AS vyhzh,cbi.Vkhh AS vkhh,cbi.Vyhzhmc AS vyhzhmc")
        .addLeftJoin("TS_UserChange t2 on  A.CreateChangeId = t2.id")
        .addField("CONCAT(t2.UserId,'/', t2.UserName) as _ys_createUserInfo")
        .addField("t2.DeptName as deptName")
        .addLeftJoin("TCW_PartnerBankInfo pbi ON pbi.id = A.PartnerBankId")//收款人银行
        .addField("pbi.Vyhzh AS hzvyhzh,pbi.Vkhh AS hzvkhh,pbi.Vyhzhmc AS hzvyhzhmc")
        .addField("(SELECT O.OrgName FROM TB_ReservedPayItem rpi LEFT JOIN TB_UserMonthPerformance R ON R.id = rpi.UserMonthPerformanceId LEFT JOIN TS_OrganizationInfo O ON O.OrgId = R.OrgId WHERE rpi.ReservedPayBid = a.bid GROUP BY O.OrgName) orgName")
        .addField("(SELECT SUM(rpi.accumulationFundCompany) FROM TB_ReservedPayItem rpi LEFT JOIN TB_UserMonthPerformance R ON R.id = rpi.UserMonthPerformanceId  WHERE rpi.ReservedPayBid = a.bid ) accumulationFundCompany")
        .addField("(SELECT SUM(rpi.accumulationFundPersonal) FROM TB_ReservedPayItem rpi LEFT JOIN TB_UserMonthPerformance R ON R.id = rpi.UserMonthPerformanceId  WHERE rpi.ReservedPayBid = a.bid ) accumulationFundPersonal")
        .addField("(SELECT SUM(rpi.accumulationFundCompany + rpi.accumulationFundPersonal) FROM TB_ReservedPayItem rpi LEFT JOIN TB_UserMonthPerformance R ON R.id = rpi.UserMonthPerformanceId  WHERE rpi.ReservedPayBid = a.bid )  accumulationTol")
        ;
        return commonApiService.searchLay(cond);
    }


    /**
     * 新增
     */
    @RequiresPermissions("tbReservedPay:add")
    @RequestMapping("/toAdd")
    @ResponseBody
    public R toAdd(HttpServletRequest request, @RequestParam Map<String, Object> params,String subData
    ) {
        try {
            logger.info("公积金付款单新增传参：" + params);
            //修改
            return tbReservedPayService.add(params,subData);
        } catch (Exception e) {
            logger.error("公积金付款单新增出错：", e.getMessage(), e);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return R.error("系统正在开小差，请联系客服，我们将第一时间为您解决！");
        }
    }


    /**
     * 修改
     */
    @RequiresPermissions("tbReservedPay:add")
    @RequestMapping("/toEdit")
    @ResponseBody
    public R toEdit(HttpServletRequest request, @RequestParam Map<String, Object> params,String subData
    ) {
        try {
            logger.info("公积金付款单修改传参：" + params);
            //修改
            return tbReservedPayService.edit(params,subData);
        } catch (Exception e) {
            logger.error("公积金付款单修改出错：", e.getMessage(), e);
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return R.error("系统正在开小差，请联系客服，我们将第一时间为您解决！");
        }
    }

    @RequestMapping("/export")
    @ResponseBody
    public R export(@ModelAttribute TbReservedPay cond, String conditions) throws Exception{

        cond.setPage(0).setSize(999999);
        String _ys_createUserInfo = ConditionUtil.getConditionValue(conditions, "_ys_createUserInfo");
        String _ys_orgName = ConditionUtil.getConditionValue(conditions, "_ys_orgName");

        if (_ys_createUserInfo != null && _ys_createUserInfo.length() != 0) {
            cond.where("t2.UserId LIKE '%" + _ys_createUserInfo + "%'" + " OR  t2.UserName LIKE '%" + _ys_createUserInfo + "%'");
        }
        if (_ys_orgName != null && _ys_orgName.length() != 0) {
            cond.where("(SELECT O.OrgName FROM TB_ReservedPayItem rpi LEFT JOIN TB_UserMonthPerformance R ON R.id = rpi.UserMonthPerformanceId LEFT JOIN TS_OrganizationInfo O ON O.OrgId = R.OrgId WHERE rpi.ReservedPayBid = a.bid GROUP BY O.OrgName) LIKE'%" + _ys_orgName + "%'" );
        }
        cond.setOrderby("A.createTime DESC")
                .addLeftJoin("YS_ProcInst Ysp ON Ysp.bid = A.bid")//流程表
                .addField("Ysp.updateTime AS updateTime1")
                .addLeftJoin("TB_CompanyBankInfo cbi ON cbi.id = A.CompanyBankId")//公司银行信息
                .addField("cbi.Vyhzh AS vyhzh,cbi.Vkhh AS vkhh,cbi.Vyhzhmc AS vyhzhmc")
                .addLeftJoin("TS_UserChange t2 on  A.CreateChangeId = t2.id")
                .addField("CONCAT(t2.UserId,'/', t2.UserName) as _ys_createUserInfo")
                .addField("t2.DeptName as deptName")
                .addLeftJoin("TCW_PartnerBankInfo pbi ON pbi.id = A.PartnerBankId")//收款人银行
                .addField("pbi.Vyhzh AS hzvyhzh,pbi.Vkhh AS hzvkhh,pbi.Vyhzhmc AS hzvyhzhmc")
                .addField("(SELECT O.OrgName FROM TB_ReservedPayItem rpi LEFT JOIN TB_UserMonthPerformance R ON R.id = rpi.UserMonthPerformanceId LEFT JOIN TS_OrganizationInfo O ON O.OrgId = R.OrgId WHERE rpi.ReservedPayBid = a.bid GROUP BY O.OrgName) orgName")
                .addField("(SELECT SUM(rpi.accumulationFundCompany) FROM TB_ReservedPayItem rpi LEFT JOIN TB_UserMonthPerformance R ON R.id = rpi.UserMonthPerformanceId LEFT JOIN TS_OrganizationInfo O ON O.OrgId = R.OrgId WHERE rpi.ReservedPayBid = a.bid ) accumulationFundCompany")
                .addField("(SELECT SUM(rpi.accumulationFundPersonal) FROM TB_ReservedPayItem rpi LEFT JOIN TB_UserMonthPerformance R ON R.id = rpi.UserMonthPerformanceId LEFT JOIN TS_OrganizationInfo O ON O.OrgId = R.OrgId WHERE rpi.ReservedPayBid = a.bid ) accumulationFundPersonal")
                .addField("(SELECT SUM(rpi.accumulationFundCompany + rpi.accumulationFundPersonal) FROM TB_ReservedPayItem rpi LEFT JOIN TB_UserMonthPerformance R ON R.id = rpi.UserMonthPerformanceId  WHERE rpi.ReservedPayBid = a.bid )  accumulationTol")
        ;
        List<Map<String, Object>> listData = cond.queryMap();


        List<YsCol> listField = new YsCol()
                .setMenuId(MenuId.TB_ReservedPay)
                .setFrontShow(YsColEnum.FrontShow.SHOW.value)
                .setOrderby("A.SortNo asc")
                .queryList();

        ExportExcelUtils<TbReservedPay> util = new ExportExcelUtils<TbReservedPay>(new TbReservedPay());

        String toFilePath = util.exportExcel(listData, listField, "公积金付款单表");
        UploadService.UploadDto dto = commonApiService.uploadLocalToServer(toFilePath);
        return R.ok(dto.getUrl());
    }


    /**
     * 删除公积金付款单
     */
    @PostMapping("/toDel")
    @ResponseBody
    public R toDel(Long id){
        try {
            return tbReservedPayService.delete(id);
        } catch (Exception e) {
            e.printStackTrace();
            return R.error("操作失败，请联系客服");
        }
    }

    @PostMapping("/flowPass")
    @ResponseBody
    public R flowPass(HttpServletRequest request, Long id){
        try {
            return tbReservedPayService.flowPass(id, ShiroUtils.getUserId());
        } catch (IllegalArgumentException e) {
            logger.error("出错：",e.getMessage(),e);
            return R.error(e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
            return R.error("服务器正在开小差，请联系客服");
        }
    }

    /**
     * 流转退回
     */
    @PostMapping("/flowRefuse")
    @ResponseBody
    public R flowRefuse(HttpServletRequest request, Long id, String reason){

        try {
            return tbReservedPayService.flowRefuse(id, reason, ShiroUtils.getUserId());
        } catch (Exception e) {
            e.printStackTrace();
            return R.error("服务器正在开小差，请联系客服");
        }
    }

    /**
     * 流转驳回
     */
    @PostMapping("/flowInit")
    @ResponseBody
    public R flowInit(HttpServletRequest request, Long id, String reason){

        try {
            return tbReservedPayService.flowInit(id, reason, ShiroUtils.getUserId());
        } catch (Exception e) {
            e.printStackTrace();
            return R.error("服务器正在开小差，请联系客服");
        }
    }

    /**
     * 打印公积金付款单
     * @param request
     * @param bid
     * @return
     * @throws Exception
     */
    @RequestMapping("/exportdy")
    @ResponseBody
    public R exportdy(HttpServletRequest request,@RequestParam("bid") String bid) throws Exception{
        TbReservedPay tbReservedPay = new TbReservedPay().setBid(bid).queryFirst();
        //人员信息
        TsUserChange tsUserChange = new TsUserChange().setId(Long.valueOf(tbReservedPay.getCreateChangeId())).queryFirst();
        //付款信息
        TbCompanyBankInfo tbCompanyBankInfo = new TbCompanyBankInfo().setId(tbReservedPay.getCompanyBankId()).queryFirst();

        //收款人
        TcwPartnerBankInfo tcwPartnerBankInfo = new TcwPartnerBankInfo().setId(tbReservedPay.getPartnerBankId()).queryFirst();

        //子件数据
        BigDecimal accumulationFundCompany = BigDecimal.ZERO;
        BigDecimal accumulationFundPersonal = BigDecimal.ZERO;
        List<Map<String, Object>> sonlist = new TbReservedPayItem()
                .setReservedPayBid(bid)
                .addLeftJoin("TB_UserMonthPerformance R ON R.id = A.UserMonthPerformanceId")//月度绩效
                .addField("A.accumulationFundCompany AS accumulationFundCompany")//公积金（单位）
                .addField("A.accumulationFundPersonal AS accumulationFundPersonal")//公积金（个人）

                .addField("R.HousingPfBase AS housingPfBase")//公积金基数
                .queryMap();
        for (Map<String, Object> son : sonlist){
            accumulationFundCompany = accumulationFundCompany.add(new BigDecimal(son.get("accumulationFundCompany").toString()));
            accumulationFundPersonal = accumulationFundPersonal.add(new BigDecimal(son.get("accumulationFundPersonal").toString()));
        }

        List<NameValueDto> listData = new ArrayList<>();
        listData.add(new NameValueDto("单据编号",bid));
        listData.add(new NameValueDto("申请人",tsUserChange.getUserId() + "/" + tsUserChange.getUserName()));
        listData.add(new NameValueDto("申请部门",tsUserChange.getDeptName()));
        listData.add(new NameValueDto("付款账号",tbCompanyBankInfo.getVyhzhmc() + "/" + tbCompanyBankInfo.getVyhzh() + "/" + tbCompanyBankInfo.getVkhh()));
        listData.add(new NameValueDto("收款账号",tcwPartnerBankInfo.getVyhzhmc() + "/" + tcwPartnerBankInfo.getVyhzh() + "/" + tcwPartnerBankInfo.getVkhh()));
        listData.add(new NameValueDto("付款类型","经营费用→公积金"));
        listData.add(new NameValueDto("付款金额",(accumulationFundCompany.add(accumulationFundPersonal)).toString()));
        listData.add(new NameValueDto("付款说明",tbReservedPay.getVbz()));
        String url = pdfService.generatePDF(bid,"公积金付款单",tbCompanyBankInfo.getVyhzhmc(), DateUtil.getYYYY_MM_DD_HH_mm_ss(tbReservedPay.getCreateTime()),listData);
        return R.ok(url);
    }

    @RequestMapping("/exportdymx")
    @ResponseBody
    public R exportdymx(@RequestParam("bid") String bid) throws Exception{

        List<Map<String, Object>> listData = new TbReservedPayItem()
                .setReservedPayBid(bid)
                .addLeftJoin("TB_UserMonthPerformance R ON R.id = A.UserMonthPerformanceId")//月度绩效
                .addField("R.pointMonth AS pointMonth")//发薪月份
                .addField("R.pointMonth AS payDate")//缴费年月
//				.addField("SUBSTRING(CONVERT(varchar, DATEADD(MONTH,1,R.pointMonth + '-01'), 23),0,8) AS payDate")//缴费年月
                .addField("A.accumulationFundCompany AS accumulationFundCompany")//公积金（单位）
                .addField("A.accumulationFundPersonal AS accumulationFundPersonal")//公积金（个人）
                .addField("R.HousingPfBase AS housingPfBase")//公积金基数

                .addLeftJoin("TS_UserInfo U ON U.UserId = R.UserId")//用户信息表
                .addField("(U.UserId + '/' + U.UserName) AS userName")//工号/姓名
                .addLeftJoin("TS_OrganizationInfo oi ON oi.OrgId = U.DeptId")//组织机构表
                .addField("oi.OrgName AS deptName")//部门

                .addLeftJoin("THR_UserArchives UAS ON UAS.UserId = R.UserId")//用户档案表
                .addField("UAS.UserState AS userState")//员工状态

                .addLeftJoin("TS_OrganizationInfo O ON O.OrgId = R.OrgId")//组织机构表
                .addField("O.OrgName AS orgName")//所属公司
                .addLeftJoin("TS_OrganizationAccount oa ON oa.OrgId = U.DeptId AND oa.Status = 1")//部门绑定账号
                .addLeftJoin("THR_OperateCompanyApply THRO ON  THRO.Vgsbm = oa.Vgsbm")
                .addField("THRO.Vgsbm AS vgsbm,THRO.vgsjc AS vgsjc")//运营公司
                .addField("R.Rank AS rank")//岗位职级
                .addField("R.Type AS type")//岗位工种(0总经理 1副总经理 2责任月薪型员工 3绩效提奖型员工 4研发人员)
                .addField("R.Category AS category")//岗位类别
                .addField("A.id AS userMonthPerformanceId")//id
                .addField("A.vbz AS vbzXq")//详情备注
                .queryMap();

        List<YsGridCol> listField = new YsGridCol()
                .setGridId(13L)
                .setFrontShow(YsColEnum.FrontShow.SHOW.value)
                .setOrderby("A.SortNo asc")
                .queryList();

        ExportMxExcelUtils<TbReservedPay> util = new ExportMxExcelUtils<TbReservedPay>(new TbReservedPay());

        String toFilePath = util.exportExcel(listData, listField, "公积金付款单明细表");
        UploadService.UploadDto dto = commonApiService.uploadLocalToServer(toFilePath);
        return R.ok(dto.getUrl());
    }
}
